
/* May 10, 盘点相关数据库表结构*/
create table INVENTORY_CHECK
(
    UUID VARCHAR2(32) not null
        constraint INVENTORY_CHECK_PK
        primary key,
    INVENTORY_CHECK_ID VARCHAR2(32),
    WERKS VARCHAR2(6),
    WHS_LOCATION_CODE VARCHAR2(6),
    INVENTORY_CHECK_TYPE VARCHAR2(2),
    INVENTORY_CHECK_CATEGORY VARCHAR2(2),
    INVENTORY_CHECK_MOLD VARCHAR2(2),
    INVENTORY_CHECK_DATE VARCHAR2(16),
    INVENTORY_CHECK_RESULT VARCHAR2(2),
    IS_REVIEW VARCHAR2(2),
    INVENTORY_REVIEW_ID VARCHAR2(32),
    INVENTORY_CHECK_SURPLUS VARCHAR2(32),
    INVENTORY_CHECK_LOSS VARCHAR2(32),
    CREATE_DATE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2),
    REQUESTER VARCHAR2(32),
    OPERATOR VARCHAR2(32),
    REVIEWER VARCHAR2(32),
    APPROVER VARCHAR2(32),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_USERNAME VARCHAR2(64),
    UPDATE_TIME DATE
)
    /

comment on table INVENTORY_CHECK is '仓库盘点任务表'
/

comment on column INVENTORY_CHECK.UUID is '仓库盘点任务UUID'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_ID is '仓库盘点任务单号'
/

comment on column INVENTORY_CHECK.WERKS is '工厂编号'
/

comment on column INVENTORY_CHECK.WHS_LOCATION_CODE is '仓库地点代码'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_DATE is '盘点日期'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/
comment on column INVENTORY_CHECK.IS_REVIEW is '是否复盘 1-初盘 2-复盘'
/
comment on column INVENTORY_CHECK.INVENTORY_REVIEW_ID is '复盘单据UUID'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_SURPLUS is '盘盈单据号'
/
comment on column INVENTORY_CHECK.INVENTORY_CHECK_LOSS is '盘亏单据号'
/

comment on column INVENTORY_CHECK.REQUESTER is '创建人工号'
/

comment on column INVENTORY_CHECK.CREATE_DATE is '创建日期'
/

comment on column INVENTORY_CHECK.SPMS_STATUS is '任务状态：1-待盘点；2-盘点中；3-复盘中；4-处理中；5-关闭(完成)'
/

comment on column INVENTORY_CHECK.OPERATOR is '申请人'
/
comment on column INVENTORY_CHECK.REVIEWER is '复核人'
/
comment on column INVENTORY_CHECK.APPROVER is '审批人'
/

comment on column INVENTORY_CHECK.CREATE_TIME is '创建时间'
/

comment on column INVENTORY_CHECK.UPDATE_USERNAME is '更新人姓名'
/

comment on column INVENTORY_CHECK.UPDATE_TIME is '更新时间'
/


create or replace trigger TR_INVENTORY_CHECK_BEF
	before update
                      on INVENTORY_CHECK
                      for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create or replace trigger TR_INVENTORY_CHECK_BEF1
	before insert
	on INVENTORY_CHECK
	for each row
DECLARE
V_NUM1 NUMBER := 0;
BEGIN
SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.INVENTORY_CHECK_ID), -3, 3)), 0) INTO V_NUM1 FROM INVENTORY_CHECK SOT
WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
  AND SOT.WERKS = :NEW.WERKS
  AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
:NEW.INVENTORY_CHECK_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/
create table INVENTORY_CHECK_DTL
(
    UUID VARCHAR2(32),
    TASK_ROW_ID VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BOX_NOTE VARCHAR2(50),
    INVENTORY_QUANTITY NUMBER,
    CHECK_QUANTITY NUMBER,
    DISCREPANCY NUMBER,
    BASIC_UNIT VARCHAR2(32),
    STORAGE_UUID VARCHAR2(32),
    REMARKS VARCHAR2(256)
)
    /

comment on table INVENTORY_CHECK_DTL is '盘点任务表明细表'
/

comment on column INVENTORY_CHECK_DTL.UUID is '盘点任务UUID'
/

comment on column INVENTORY_CHECK_DTL.TASK_ROW_ID is '盘点任务行号'
/

comment on column INVENTORY_CHECK_DTL.MATERIAL_NO is '物料编号'
/

comment on column INVENTORY_CHECK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column INVENTORY_CHECK_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column INVENTORY_CHECK_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column INVENTORY_CHECK_DTL.DISCREPANCY is '差异数量'
/
comment on column INVENTORY_CHECK_DTL.BASIC_UNIT is '基本单位'
/

comment on column INVENTORY_CHECK_DTL.STORAGE_UUID is '储位UUID'
/

comment on column INVENTORY_CHECK_DTL.REMARKS is '备注'
/

create table INVENTORY_CHECK_ACTION
(
    UUID VARCHAR2(32) not null
        constraint INVENTORY_CHECK_ACTION_PK
        primary key,
    SPMS_ID VARCHAR2(32),
    INVENTORY_ACTION_TYPE VARCHAR2(6),
    WERKS VARCHAR2(6),
    WHS_LOCATION_CODE VARCHAR2(6),
    INVENTORY_CHECK_ID VARCHAR2(32),
    INVENTORY_CHECK_RESULT VARCHAR2(2),
    INVENTORY_CHECK_TYPE VARCHAR2(2),
    INVENTORY_CHECK_CATEGORY VARCHAR2(2),
    INVENTORY_CHECK_MOLD VARCHAR2(2),
    CREATE_DATE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2),
    REQUESTER VARCHAR2(32),
    OPERATOR VARCHAR2(32),
    REVIEWER VARCHAR2(32),
    APPROVER VARCHAR2(32),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_USERNAME VARCHAR2(64),
    UPDATE_TIME DATE,
    JUSTIFICATION VARCHAR2(255),
    REMARKS VARCHAR2(255)
)
    /

comment on table INVENTORY_CHECK_ACTION is '仓库盘盈盘亏'
/

comment on column INVENTORY_CHECK_ACTION.UUID is '仓库盘盈盘亏任务UUID'
/
COMMENT ON COLUMN INVENTORY_CHECK_ACTION.INVENTORY_ACTION_TYPE IS '盘盈盘亏类别 1-盘盈 2-盘亏'
/
comment on column INVENTORY_CHECK_ACTION.SPMS_ID is '仓库盘盈盘亏单号'
/

comment on column INVENTORY_CHECK_ACTION.WERKS is '工厂编号'
/

comment on column INVENTORY_CHECK_ACTION.WHS_LOCATION_CODE is '仓库地点代码'
/
comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/
comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/
comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/
comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_ID is '盘点单据号'
/

comment on column INVENTORY_CHECK_ACTION.REQUESTER is '创建人工号'
/

comment on column INVENTORY_CHECK_ACTION.CREATE_DATE is '创建日期'
/

comment on column INVENTORY_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-审批中；3-审批不通过；4-审批通过(待上传SAP)；5-关闭(完成)'
/

comment on column INVENTORY_CHECK_ACTION.OPERATOR is '申请人'
/
comment on column INVENTORY_CHECK_ACTION.REVIEWER is '复核人'
/
comment on column INVENTORY_CHECK_ACTION.APPROVER is '审批人'
/

comment on column INVENTORY_CHECK_ACTION.CREATE_TIME is '创建时间'
/

comment on column INVENTORY_CHECK_ACTION.UPDATE_USERNAME is '更新人姓名'
/

comment on column INVENTORY_CHECK_ACTION.UPDATE_TIME is '更新时间'
/
comment on column INVENTORY_CHECK_ACTION.JUSTIFICATION is '申请原因'
/
comment on column INVENTORY_CHECK_ACTION.REMARKS is '备注'
/


create or replace trigger TR_INVENTORY_CHECK_ACTION_BEF
    before update
                      on INVENTORY_CHECK_ACTION
                      for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create or replace trigger TR_INVENTORY_CHECK_ACTION_ID
    before insert
    on INVENTORY_CHECK_ACTION
    for each row
DECLARE
V_NUM1 NUMBER := 0;
BEGIN
SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM INVENTORY_CHECK_ACTION SOT
WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
  AND SOT.WERKS = :NEW.WERKS
  AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
:NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table INVENTORY_CHECK_ACTION_DTL
(
    UUID VARCHAR2(32),
    TASK_ROW_ID VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BOX_NOTE VARCHAR2(50),
    INVENTORY_QUANTITY NUMBER,
    CHECK_QUANTITY NUMBER,
    DISCREPANCY NUMBER,
    BASIC_UNIT VARCHAR2(32),
    STORAGE_UUID VARCHAR2(32),
    REMARKS VARCHAR2(256)
)
    /

comment on table INVENTORY_CHECK_ACTION_DTL is '盘盈盘亏明细表'
/

comment on column INVENTORY_CHECK_ACTION_DTL.UUID is '盘盈盘亏UUID'
/

comment on column INVENTORY_CHECK_ACTION_DTL.TASK_ROW_ID is '盘盈盘亏任务行号'
/

comment on column INVENTORY_CHECK_ACTION_DTL.MATERIAL_NO is '物料编号'
/

comment on column INVENTORY_CHECK_ACTION_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column INVENTORY_CHECK_ACTION_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.DISCREPANCY is '差异数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.BASIC_UNIT is '基本单位'
/

comment on column INVENTORY_CHECK_ACTION_DTL.STORAGE_UUID is '储位UUID'
/

comment on column INVENTORY_CHECK_ACTION_DTL.REMARKS is '备注'
/


alter table INVENTORY_CHECK_ACTION
    add POSTING_DATE VARCHAR2(16)
/

comment on column INVENTORY_CHECK_ACTION.POSTING_DATE is '凭证中的过账日期-用户选'
/

alter table INVENTORY_CHECK_ACTION
    add COST_CENTER VARCHAR2(16)
/

comment on column INVENTORY_CHECK_ACTION.COST_CENTER is '成本中心'
/

alter table INVENTORY_CHECK_ACTION
    add MOVE_TYPE VARCHAR2(6)
/

comment on column INVENTORY_CHECK_ACTION.MOVE_TYPE is '移动类型'
/

alter table INVENTORY_CHECK_ACTION
    add VOUCHER_TEXT VARCHAR2(255)
/

comment on column INVENTORY_CHECK_ACTION.VOUCHER_TEXT is '凭证抬头文本'
/

alter table INVENTORY_CHECK_ACTION
    add SAP_VOUCHER_NUMBER VARCHAR2(16)
/

comment on column INVENTORY_CHECK_ACTION.SAP_VOUCHER_NUMBER is 'SAP物料凭证-提交SAP成功后回写'
/

alter table INVENTORY_CHECK_ACTION
    add SAP_VOUCHER_YEAR VARCHAR2(4)
/

comment on column INVENTORY_CHECK_ACTION.SAP_VOUCHER_YEAR is '会计年份/SAP凭证年份'
/


alter table INVENTORY_CHECK_ACTION_DTL
    add RELATE_ACCOUNT VARCHAR2(16)
/

comment on column INVENTORY_CHECK_ACTION_DTL.RELATE_ACCOUNT is '供应商或债权人的帐号'
/

alter table INVENTORY_CHECK_ACTION_DTL
    add VOUCHER_ITEM_TEXT VARCHAR2(255)
/

comment on column INVENTORY_CHECK_ACTION_DTL.VOUCHER_ITEM_TEXT is '项目文本'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_ACTION_TYPE is '盘盈盘亏类别 701-盘盈 702-盘亏'
/

comment on column INVENTORY_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-审批中；3-审批通过(待上传SAP)；4-关闭(完成)；5-审批不通过；6-提交SAP成功；'
/

comment on column INVENTORY_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-审批中；3-审批通过(待上传SAP)；4-关闭(完成/提交SAP成功)；5-审批不通过；6-提交SAP失败；'
/

alter table WHS_MOVE
    add SAP_VOUCHER_YEAR VARCHAR2(16)
/

comment on column WHS_MOVE.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/
comment on column WHS_OPERATE_LOG.OPERATION_TYPE is '操作类型:1入库(加仓库库存);2上架(加储位库存);3下架(减储位库存);4出库(减仓库库存);5移位(出储位);6移位(进储位);7盘点(盘盈入储位);8盘点(盘亏出储位);9盘点(盘盈入仓库);10盘点(盘亏出仓库);11移仓(出储位);12移仓(入储位);13移仓(出仓库);14移仓(入仓库);'
/











